set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
-- 网点进港汇总
with entry_port_aging_sign_send as (
    select end_agent_code
         , max(end_agent_name)             as end_agent_name
         , end_center_code
         , max(end_center_name)            as end_center_name
         , end_franchisee_code
         , max(end_franchisee_name)        as end_franchisee_name
         , final_sign_network_code
         , max(final_sign_network_name)    as final_sign_network_name
         , max(order_source_code)          as order_source_code
         , order_source_name
         ,deliver_shift  --班次
         ,planned_departure_time  --规划接件时间
         ,planned_arrival_time    --规划到件时间
         , min(is_common)                  as is_common
         , count(1)                        as need_departure_sum    --发件总量
         , sum(if(is_departure = 1, 1, 0)) as on_time_departure_sum   --准点接件梁
         , sum(if(is_departure = 0, 1, 0)) as over_time_departure_sum  -- 超时接件梁
         , sum(if(is_departure_type = 0, 1, 0)) as no_scan_departure_sum  -- 漏扫接件量
         , sum(if(is_departure_type = 1, 1, 0)) as operate_later_departure_sum  -- 操作不及时接件量
         , sum(if(is_arrival = 1, 1, 0))   as on_time_arrival_sum     --准点到达量
         , sum(if(is_arrival = 0, 1, 0))   as over_time_arrival_sum   --超时到达量
         , sum(if(is_arrival_type = 0, 1, 0))   as no_scan_arrival_sum   --超时到达量
         , sum(if(is_arrival_type = 1, 1, 0))   as operate_later_arrival_sum   --超时到达量
         , dt
    from jms_dm.dm_network_entry_port_aging_sign_detail_dt
    where dt >= date_sub('{{ execution_date | cst_ds }}', 14)
      and dt <= '{{ execution_date | cst_ds }}'
    group by dt,end_agent_code,end_center_code,end_franchisee_code,final_sign_network_code,order_source_name,deliver_shift,planned_departure_time,planned_arrival_time
),
     entry_port_aging_sign_sign as (
         select end_agent_code
              , max(end_agent_name)                      as end_agent_name
              , end_center_code
              , max(end_center_name)                      as end_center_name
              , end_franchisee_code
              , max(end_franchisee_name)                 as end_franchisee_name
              , final_sign_network_code
              , max(final_sign_network_name)             as final_sign_network_name
              , max(order_source_code)                   as order_source_code
              , order_source_name
              ,warehouse_end_time  --派仓结束时间（规划出仓时间）
              ,deliver_shift  --派件班次
              , min(is_common)                           as is_common   --是否共配
              , count(1)                                 as need_deliver_sum
              , sum(if(is_deliver = 1, 1, 0))            as on_time_deliver_sum
              , sum(if(is_deliver=1 and deliver_township_id is not null , 1, 0))            as on_time_deliver_township_sum
              , sum(if(is_deliver = 1 and deliver_township_id is  null, 1, 0))            as on_time_no_township_deliver_sum
              , sum(if(is_deliver_type = 0, 1, 0))            as no_scan_deliver_sum
              , sum(if(is_deliver_type = 1, 1, 0))            as operate_later_deliver_sum
              , sum(if(is_deliver = 0, 1, 0))            as over_time_deliver_sum
              , sum(if(is_aging_sign_on_time = 1, 1, 0)) as aging_sign_on_time_sum  --准点汇总
              , sum(if(is_aging_sign_on_time = 0, 1, 0)) as aging_sign_over_time_sum  -- 不准点汇总
              -- 新增
              ,sum(if(is_aging_sign_type=1 and terminal_type='驿站',1,0 ))  as  yizhan_delay  --驿站延误
              ,sum(if(is_aging_sign_type=1 and terminal_type='柜机',1,0 ))  as  guiji_delay   --柜机延误
              ,sum(if(is_aging_sign_type=1 and terminal_type is null,1,0 )) as  shsm_delay   --送货上门延误
              ,sum(if(is_active_sign_on_time=1,1,0)) as                         active_sign_on_time_sum  --实际签收准点总量
              ,sum(if(is_active_sign_on_time=0,1,0)) as                         active_sign_over_time_sum  --实际不准点签收总量
              ,max(deadline_signing_time) as                                    deadline_signing_time  --截止签收时间
              ,sum(if(aging_sign_time is null and difficult_type is  null,1,0)) as                unsign_count  --未签收其它总量
              ,sum(if(aging_sign_time is null and difficult_type is  not null,1,0)) as difficult_over_time_count  --未签收问题件汇总
              , final_plan_sign_date as dt
         from jms_dm.dm_network_entry_port_aging_sign_detail_dt
         where dt >= date_sub('{{ execution_date | cst_ds }}', 29)
           and dt <= '{{ execution_date | cst_ds }}'
           and final_plan_sign_date >= date_sub('{{ execution_date | cst_ds }}', 14)
           and final_plan_sign_date <= '{{ execution_date | cst_ds }}'
         group by final_plan_sign_date, end_agent_code, end_center_code, end_franchisee_code, final_sign_network_code,
                  order_source_name,warehouse_end_time,deliver_shift
         )
insert overwrite table jms_dm.dm_network_entry_port_aging_sign_network_sum_dt partition (dt)
-- 网点进港时效网点汇总
select
                end_agent_code                                                -- 末端代理区code
              , max(end_agent_name) as end_agent_name                         --末端代理区
              , end_center_code                                               --末端中心code
              , max(end_center_name) as end_center_name                       --末端中心
              , end_franchisee_code                                           --末端加盟商
              , max(end_franchisee_name) as end_franchisee_name               --末端加盟商
              , final_sign_network_code                                       --末端网点
              , max(final_sign_network_name) as final_sign_network_name       --末端网点
              , max(order_source_code) as order_source_code                   --订单来源code
              , order_source_name                                             --订单来源名字
              , min(is_common) as is_common                                   --是否共配
              , sum(need_departure_sum) as need_departure_sum                  --应接件件数
              , sum(on_time_departure_sum) as on_time_departure_sum            --准点接件个数
              , sum(over_time_departure_sum) as over_time_departure_sum        --延误接件个数
              , sum(on_time_arrival_sum) as on_time_arrival_sum                --准点到件数
              , sum(over_time_arrival_sum) as over_time_arrival_sum            --延误到件数
              ,sum(need_deliver_sum)  as need_deliver_sum                     --应出仓总量
              ,sum(on_time_deliver_sum)  as on_time_deliver_sum               --准点出仓总量
              ,sum(over_time_deliver_sum)  as over_time_deliver_sum           --延误出仓总量
              ,sum(aging_sign_on_time_sum)  as aging_sign_on_time_sum         --准点签收总量
              ,sum(aging_sign_over_time_sum)  as aging_sign_over_time_sum     -- 延误签收总量
              , dt as date_time
              , deliver_shift                                                  --班次
              , max(planned_departure_time) as planned_departure_time         --规划接件时间
              , max(planned_arrival_time) as planned_arrival_time             --规划到达时间
              , sum(no_scan_departure_sum) as no_scan_departure_sum           --接件漏扫量
              , sum(operate_later_departure_sum) as operate_later_departure_sum             --接件操作不及时量
              , sum(no_scan_arrival_sum) as no_scan_arrival_sum                             --到件漏扫量
              , sum(operate_later_arrival_sum) as operate_later_arrival_sum                --到件操作不及时量
              , max(warehouse_end_time)    as warehouse_end_time                           --规划出仓时间
              , sum(no_scan_deliver_sum)    as no_scan_deliver_sum                         --出仓漏扫量
              , sum(operate_later_deliver_sum)    as operate_later_deliver_sum             --出仓操作不及时量
              , sum(on_time_deliver_township_sum)    as on_time_deliver_township_sum       --乡镇出仓及时量
              , sum(on_time_no_township_deliver_sum)    as on_time_no_township_deliver_sum --非乡镇出仓及时量
             -- 新增
              ,sum(yizhan_delay) as yizhan_delay  --驿站延误
              ,sum(guiji_delay) as guiji_delay   --柜机延误
              ,sum(shsm_delay) as shsm_delay   --送货上门延误
              ,sum(active_sign_on_time_sum) as active_sign_on_time_sum  --实际签收准点总量
              ,sum(active_sign_over_time_sum) as active_sign_over_time_sum  --实际不准点签收总量
              ,max(deadline_signing_time)  as deadline_signing_time  --截止签收时间
              ,sum(unsign_count) as unsign_count  --未签收总量
              ,sum(difficult_over_time_count) as difficult_over_time_count  --延误问题件汇总
              , dt
from (
         select a.end_agent_code                        -- 末端代理区code
              , a.end_agent_name                        --末端代理区
              , a.end_center_code                       --末端中心code
              , a.end_center_name                       --末端中心
              , a.end_franchisee_code                   --末端加盟商
              , a.end_franchisee_name                   --末端加盟商
              , a.final_sign_network_code               --末端网点
              , a.final_sign_network_name               --末端网点
              , a.order_source_code                     --订单来源code
              , a.order_source_name                     --订单来源名字
              , a.is_common                             --是否共配
              , a.need_departure_sum                    --应接件件数
              , a.on_time_departure_sum                 --准点接件个数
              , a.over_time_departure_sum               --延误接件个数
              , a.on_time_arrival_sum                   --准点到件数
              , a.over_time_arrival_sum                 --延误到件数
              , 0    as need_deliver_sum                -- 应出仓总量
              , 0    as on_time_deliver_sum             --准点出仓总量
              , 0    as over_time_deliver_sum           --延误出仓总量
              , 0    as aging_sign_on_time_sum          --准点签收总量
              , 0    as aging_sign_over_time_sum        -- 延误签收总量
              , a.dt as date_time

              , a.deliver_shift                         --班次
              , a.planned_departure_time                --规划接件时间
              , a.planned_arrival_time                  --规划到达时间
              , a.no_scan_departure_sum                 --接件漏扫量
              , a.operate_later_departure_sum           --接件操作不及时量
              , a.no_scan_arrival_sum                   --到件漏扫量
              , a.operate_later_arrival_sum             --到件操作不及时量

              , null    as warehouse_end_time              --规划出仓时间
              , 0    as no_scan_deliver_sum             --出仓漏扫量
              , 0    as operate_later_deliver_sum       --出仓操作不及时量
              , 0    as on_time_deliver_township_sum    --乡镇出仓及时量
              , 0    as on_time_no_township_deliver_sum --非乡镇出仓及时量
              , 0 as yizhan_delay  --驿站延误
              , 0 as guiji_delay   --柜机延误
              , 0 as shsm_delay   --送货上门延误
              , 0 as active_sign_on_time_sum  --实际签收准点总量
              , 0 as active_sign_over_time_sum  --实际不准点签收总量
              , null as deadline_signing_time  --截止签收时间
              ,0     as unsign_count  --未签收总量
              ,0     as difficult_over_time_count  --延误问题件汇总

              , a.dt
         from entry_port_aging_sign_send a
         union all
         select end_agent_code                      -- 末端代理区code
              , end_agent_name                      --末端代理区
              , end_center_code                     --末端中心code
              , end_center_name                     --末端中心
              , end_franchisee_code                 --末端加盟商
              , end_franchisee_name                 --末端加盟商
              , final_sign_network_code             --末端网点
              , final_sign_network_name             --末端网点
              , order_source_code                   --订单来源code
              , order_source_name                   --订单来源名字
              , is_common                           --是否共配
              , 0    as need_departure_sum          --应接件件数
              , 0    as on_time_departure_sum       --准点接件个数
              , 0    as over_time_departure_sum     --延误接件个数
              , 0    as on_time_arrival_sum         --准点到件数
              , 0    as over_time_arrival_sum       --延误到件数

              , need_deliver_sum                    -- 应出仓总量
              , on_time_deliver_sum                 --准点出仓总量
              , over_time_deliver_sum               --延误出仓总量
              , aging_sign_on_time_sum             --准点签收总量
              , aging_sign_over_time_sum            -- 延误签收总量

              , dt   as date_time

              , deliver_shift                       --班次
              , null as planned_departure_time      --规划接件时间
              , null as planned_arrival_time        --规划到达时间
              , 0    as no_scan_departure_sum       --接件漏扫量
              , 0    as operate_later_departure_sum --接件操作不及时量
              , 0    as no_scan_arrival_sum         --到件漏扫量
              , 0    as operate_later_arrival_sum   --到件操作不及时量

              , warehouse_end_time                  --规划出仓时间
              , no_scan_deliver_sum                 --出仓漏扫量
              , operate_later_deliver_sum           --出仓操作不及时量
              , on_time_deliver_township_sum        --乡镇出仓及时量
              , on_time_no_township_deliver_sum     --非乡镇出仓及时量
              --
              ,yizhan_delay  --驿站延误
              ,guiji_delay   --柜机延误
              ,shsm_delay   --送货上门延误
              ,active_sign_on_time_sum  --实际签收准点总量
              ,active_sign_over_time_sum  --实际不准点签收总量
              ,deadline_signing_time  --截止签收时间
              ,unsign_count  --未签收总量
              ,difficult_over_time_count  --延误问题件汇总
              , dt

         from entry_port_aging_sign_sign b
     )tmp
     group by dt, end_agent_code, end_center_code, end_franchisee_code, final_sign_network_code,
                  order_source_name,deliver_shift
distribute by dt,pmod(hash(rand()),3);